package com.example.projectmanagement.repository.study;

import com.example.projectmanagement.model.study.Question;
import com.example.projectmanagement.model.User;
import com.example.projectmanagement.model.study.Course;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;

@Repository
public interface QuestionRepository extends JpaRepository<Question, Long> {
    @Query("SELECT q FROM Question q LEFT JOIN FETCH q.user LEFT JOIN FETCH q.course WHERE q.user = :user")
    Page<Question> findByUserWithUserAndCourse(@Param("user") User user, Pageable pageable);
    Page<Question> findByCourse(Course course, Pageable pageable);
    Page<Question> findByCourse_Id(Long courseId, Pageable pageable);
    Page<Question> findByStatus(String status, Pageable pageable);
    Page<Question> findByTitleContainingIgnoreCase(String title, Pageable pageable);
    
    @Query("SELECT q FROM Question q WHERE q.course.id = :courseId AND q.lessonId = :lessonId")
    Page<Question> findByCourseIdAndLessonId(@Param("courseId") Long courseId, @Param("lessonId") Long lessonId, Pageable pageable);
    
    @Query("SELECT COUNT(q) FROM Question q WHERE q.user.id = :userId")
    Integer countQuestionsByUserId(@Param("userId") Long userId);
    
    @Query("SELECT q FROM Question q LEFT JOIN FETCH q.user LEFT JOIN FETCH q.course")
    Page<Question> findAllWithUserAndCourse(Pageable pageable);
    
    @Query("SELECT q FROM Question q LEFT JOIN FETCH q.user LEFT JOIN FETCH q.course WHERE q.course.id = :courseId")
    Page<Question> findByCourseIdWithUserAndCourse(@Param("courseId") Long courseId, Pageable pageable);
    
    @Query("SELECT q FROM Question q LEFT JOIN FETCH q.user LEFT JOIN FETCH q.course WHERE q.status = :status")
    Page<Question> findByStatusWithUserAndCourse(@Param("status") String status, Pageable pageable);
    
    @Query("SELECT q FROM Question q LEFT JOIN FETCH q.user LEFT JOIN FETCH q.course WHERE q.title LIKE %:keyword%")
    Page<Question> findByTitleContainingIgnoreCaseWithUserAndCourse(@Param("keyword") String keyword, Pageable pageable);
    
    @Query("SELECT q FROM Question q LEFT JOIN FETCH q.user LEFT JOIN FETCH q.course WHERE q.id = :id")
    Optional<Question> findByIdWithUserAndCourse(@Param("id") Long id);
    
    /**
     * 获取问题状态统计
     */
    @Query("SELECT COUNT(q) FROM Question q WHERE q.status = 'OPEN' " +
           "UNION ALL " +
           "SELECT COUNT(q) FROM Question q WHERE q.status = 'RESOLVED' " +
           "UNION ALL " +
           "SELECT COUNT(q) FROM Question q WHERE q.status = 'CLOSED'")
    List<Long> getQuestionStatusCounts();
}